import calendar
import time
from time import mktime

import datetime

from django.db import connection

from costreport.models import CostDailyReport, CostHourReport
from users.models import UserProfile, AdPriceRate, ExoProfile
from utils import ConversionUtils
from utils.CacheUtils import write_adrecode_to_cache

__author__ = 'cwd'
__date__ = '2018/3/7 15:48'

import json

from utils.Constants import Constants

from designad.models import Categories, GeographicLocation, Languages, Browsers, MobileCarriers, OperatingSystems, \
    Devices, AdvertiserAdType, DesignAdList, AdVariation, AdGoals, SitesData, UserAdFileLibrary, ad_variation_data, \
    SitesTempData, AdUrl, AdCreative
from collectiondata.models import MinimumPrices
from costtrendreport.models import CostDailyTrendReport,CostHourTrendReport
from utils.HttpUtils import HttpUtils


budget_list = [
               20000, 15000, 10000,
               5000, 2000, 1000,
               750, 500, 200,
               100
               ]


class InitData(object):
    # 获取所有的广告信息
    def getAllCamp(none):
        try:
            url = Constants.CAMPAIGNS_GET_URL
            userList = UserProfile.objects.all()
            today = datetime.datetime.today()
            for user in userList:
                userId = user.id
                if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                    n = HttpUtils.getHttp(url, userId)
                    if n is not None:
                        if n.status_code == 200:
                            body = json.loads(n.text)
                            result = body['result']
                            for designAd in result:
                                ad_camp = DesignAdList.objects.filter(campaign_id=result[designAd]['id'])
                                if ad_camp.count() > 0:
                                    if ad_camp.exists() and ad_camp[0].status != 4 and ad_camp[0].status != 5 and ad_camp[0].status != 6 and ad_camp[0].status != 7 and ad_camp[0].status != 8:
                                        # 防止权限同步不及时
                                        cursor = connection.cursor()
                                        cursor.execute(
                                            'update users_userprofile_groups set group_id = 1 where userprofile_id = ' + str(
                                                userId))

                                        ad_camp.update(
                                                      priceModel=result[designAd]['pricing_model'],
                                                      adStyle=AdvertiserAdType.objects.filter(id=result[designAd]['advertiser_ad_type'])[0].name,
                                                      status=result[designAd]['status'],
                                                      checked=result[designAd]['checked'],
                                                      isPost=1,
                                                      # price=ConversionUtils.exoToMe(userId, result[designad]['price'], campaign.created_at)
                                                      )
                                    if ad_camp[0].status == 4 or ad_camp[0].status == 5 or ad_camp[0].status == 6 or ad_camp[0].status == 7 or ad_camp[0].status == 8:
                                        data = HttpUtils.putHttp(Constants.CAMPAIGNS_PAUSE_URL,
                                                                 {"campaign_ids": [ad_camp[0].campaign_id]},
                                                                 userId
                                                                 )
                                        if data is not None:
                                            if data.status_code == 200:
                                                print('暂停未过审的广告活动{0}成功！！'.format(ad_camp[0].adName))
                                            else:
                                                print('暂停未过审的广告活动{0}失败！！'.format(ad_camp[0].adName))
                                else:
                                    maxDailyBudget = 0
                                    temp_bud = 0
                                    ad_results = HttpUtils.getHttp(Constants.CAMPAIGNS_GET_ONE_URL.format(result[designAd]['id']), userId)
                                    if ad_results is not None:
                                        if ad_results.status_code == 200:
                                            body = json.loads(ad_results.text)
                                            ad_result = body['result']
                                            publisher_ad_types = []
                                            designAdList = DesignAdList()
                                            designAdList.adUser_id = userId
                                            designAdList.campaign_id = ad_result.get('campaign').get('id')
                                            designAdList.adName = ad_result.get('campaign').get('name')
                                            if str(ad_result.get('campaign').get('size')).find('300x250') >= 0:
                                                designAdList.adStyle = 'Banner - 300x250'
                                            elif str(ad_result.get('campaign').get('size')).find('120x600') >= 0:
                                                designAdList.adStyle = 'Banner - 120x600'
                                            elif str(ad_result.get('campaign').get('size')).find('160x600') >= 0:
                                                designAdList.adStyle = 'Banner - 160x600'
                                            elif str(ad_result.get('campaign').get('size')).find('250x250') >= 0:
                                                designAdList.adStyle = 'Banner - 250x250'
                                            elif str(ad_result.get('campaign').get('size')).find('315x300') >= 0:
                                                designAdList.adStyle = 'Banner - 315x300'
                                            elif str(ad_result.get('campaign').get('size')).find('900x250') >= 0:
                                                designAdList.adStyle = 'Banner - 900x250'
                                            elif str(ad_result.get('campaign').get('size')).find('970x250') >= 0:
                                                designAdList.adStyle = 'Billboard Banner - 970x250'
                                            elif str(ad_result.get('campaign').get('size')).find('300x50') >= 0:
                                                designAdList.adStyle = 'Mobile - 300x50'
                                            elif str(ad_result.get('campaign').get('size')).find('300x100') >= 0:
                                                designAdList.adStyle = 'Mobile - 300x100'
                                            elif str(ad_result.get('campaign').get('size')).find('250x150') >= 0:
                                                designAdList.adStyle = 'Banner - Web Instant Message'
                                            elif str(ad_result.get('campaign').get('size')).find('728x90') >= 0:
                                                designAdList.adStyle = 'Banner - 728x90'
                                            elif str(ad_result.get('campaign').get('size')).find('468x60') >= 0:
                                                designAdList.adStyle = 'Banner - 468x60'
                                            elif str(ad_result.get('campaign').get('size')).find('null') >= 0:
                                                if ad_result.get('publisher_ad_types') == []:
                                                    designAdList.adStyle = 'Tab - Direct Link'
                                                else:
                                                    designAdList.adStyle = 'Popunders'

                                            temp_budget = (
                                                float(ConversionUtils.exoToMe(userId,
                                                                              float(int(ad_result.get('campaign').get('max_daily_budget_reset'))/100),
                                                                              today)))
                                            for temp in budget_list:
                                                if temp_budget > temp:
                                                    maxDailyBudget = temp_bud
                                                    break
                                                elif temp_budget == temp:
                                                    maxDailyBudget = temp
                                                    break
                                                else:
                                                    # 不能低于100
                                                    maxDailyBudget = 100
                                                temp_bud = temp

                                            designAdList.maxDailyBudget = maxDailyBudget
                                            designAdList.status = ad_result.get('campaign').get('status')
                                            designAdList.priceModel = ad_result.get('campaign').get('pricing_model')
                                            designAdList.price = float(ConversionUtils.exoToMe(userId, ad_result.get('campaign').get('price'),
                                                                              today))/100
                                            if ad_result.get('campaign').get('pricing_model') != 1:
                                                if ad_result.get('campaign').get('capping').get('quantity') is not None:
                                                    designAdList.impressionsEnabled = 1
                                                    designAdList.impressions = ad_result.get('campaign').get('capping').get('quantity')
                                                    designAdList.minutes = ad_result.get('campaign').get('capping').get('frequency')
                                            designAdList.checked = ad_result.get('campaign').get('checked')
                                            designAdList.isPost = 1
                                            if ad_result.get('publisher_ad_types') != []:
                                                for publisher_ad_type in ad_result.get('publisher_ad_types'):
                                                    publisher_ad_types.append(publisher_ad_type['id'])
                                            else:
                                                if str(ad_result.get('campaign').get('size')).find('300x250') >= 0:
                                                    publisher_ad_types.extend([2, 9, 17, 10, 24, 27])
                                                elif str(ad_result.get('campaign').get('size')).find('120x600') >= 0 \
                                                    or str(ad_result.get('campaign').get('size')).find('160x600') >= 0 \
                                                      or str(ad_result.get('campaign').get('size')).find('250x250') >= 0 \
                                                      or str(ad_result.get('campaign').get('size')).find('315x300') >= 0  \
                                                        or str(ad_result.get('campaign').get('size')).find('900x250') >= 0:
                                                    publisher_ad_types.extend([2, 17])
                                                elif str(ad_result.get('campaign').get('size')).find('468x60') >= 0 \
                                                      or str(ad_result.get('campaign').get('size')).find('728x90') >= 0:
                                                    publisher_ad_types.extend([2, 17, 27])
                                                elif str(ad_result.get('campaign').get('size')).find('300x50') >= 0:
                                                    publisher_ad_types.extend([10])
                                                elif str(ad_result.get('campaign').get('size')).find('300x100') >= 0:
                                                    publisher_ad_types.extend([10, 14])
                                                elif str(ad_result.get('campaign').get('media_storage_template')).find('link') >= 0:
                                                    if ad_result.get('campaign').get('pricing_model') != 1:
                                                        publisher_ad_types.extend([3, 12])
                                            designAdList.publisher_ad_types = str(publisher_ad_types).replace('[', '').replace(']', '')

                                            sites = []
                                            for site in ad_result.get('sites').get('targeted'):
                                                sites.append(site.get('url'))
                                            for site in ad_result.get('sites').get('blocked'):
                                                sites.append('-{0}'.format(site.get('url')))
                                            designAdList.sites = str(sites).replace('[', '').replace(']', '').replace(',', '\r\n').replace("'", "")
                                            DesignAdList.save(designAdList)
                                            ad_design = DesignAdList.objects.filter(campaign_id=ad_result.get('campaign').get('id'))
                                            if ad_design.count() > 0:
                                                if ad_result.get('categories') != []:
                                                    cursor = connection.cursor()
                                                    for categorie in ad_result.get('categories').get('targeted'):
                                                        if categorie.get('id') == 2:
                                                            categorie_ls = Categories.objects.filter(parent=2)
                                                            for categorie_l in categorie_ls:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_categorieslist(designadlist_id,categories_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(categorie_l.id) + ')')
                                                        elif categorie.get('id') == 472:
                                                            categorie_ls = Categories.objects.filter(parent=472)
                                                            for categorie_l in categorie_ls:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_categorieslist(designadlist_id,categories_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(categorie_l.id) + ')')
                                                        elif categorie.get('id') == 494:
                                                            categorie_ls = Categories.objects.filter(parent=494)
                                                            for categorie_l in categorie_ls:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_categorieslist(designadlist_id,categories_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(categorie_l.id) + ')')
                                                        else:
                                                            cursor.execute(
                                                                'INSERT  INTO designad_designadlist_categorieslist(designadlist_id,categories_id) VALUE(' +
                                                                str(ad_design[0].id) + ',' + str(categorie.get('id')) + ')')

                                                if ad_result.get('browsers') == []:
                                                    raw_list = DesignAdList.objects.raw('SELECT id FROM designad_browsers')
                                                    for raw_l in raw_list:
                                                        cursor = connection.cursor()
                                                        cursor.execute('INSERT  INTO designad_designadlist_browserslist(designadlist_id,browsers_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')

                                                else:
                                                    for browser in ad_result.get('browsers').get('targeted'):
                                                        cursor = connection.cursor()
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_browserslist(designadlist_id,browsers_id) VALUE('+str(ad_design[0].id)+','+str(browser.get('id'))+')')

                                                if ad_result.get('carriers') == []:
                                                    raw_list = DesignAdList.objects.raw(
                                                        'SELECT id FROM designad_mobilecarriers')
                                                    cursor = connection.cursor()
                                                    for raw_l in raw_list:
                                                        cursor.execute('INSERT  INTO designad_designadlist_mobilecarrierslist(designadlist_id,mobilecarriers_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')
                                                else:
                                                    cursor = connection.cursor()
                                                    for browser in ad_result.get('carriers').get('targeted'):
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_mobilecarrierslist(designadlist_id,mobilecarriers_id) VALUE(' +
                                                            str(ad_design[0].id) + ',' + str(browser.get('id')) + ')')

                                                if ad_result.get('countries') == []:
                                                    raw_list = DesignAdList.objects.raw(
                                                        'SELECT id FROM designad_geographiclocation')
                                                    cursor = connection.cursor()
                                                    for raw_l in raw_list:
                                                        cursor.execute('INSERT  INTO designad_designadlist_geographiclocationlist(designadlist_id,geographiclocation_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')
                                                else:
                                                    cursor = connection.cursor()
                                                    for browser in ad_result.get('countries').get('targeted'):
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_geographiclocationlist(designadlist_id,geographiclocation_id) VALUE(' +
                                                            str(ad_design[0].id) + ',' + str(browser.get('id')) + ')')

                                                if ad_result.get('operating_systems') == []:
                                                    raw_list = DesignAdList.objects.raw(
                                                        'SELECT id FROM designad_operatingsystems')
                                                    cursor = connection.cursor()
                                                    for raw_l in raw_list:
                                                        cursor.execute('INSERT  INTO designad_designadlist_operatingsystemlist(designadlist_id,operatingsystems_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')
                                                else:
                                                    cursor = connection.cursor()
                                                    for browser in ad_result.get('operating_systems').get('targeted'):
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_operatingsystemlist(designadlist_id,operatingsystems_id) VALUE(' +
                                                            str(ad_design[0].id) + ',' + str(browser.get('id')) + ')')

                                                if ad_result.get('devices') == []:
                                                    raw_list = DesignAdList.objects.raw(
                                                        'SELECT id FROM designad_devices')
                                                    cursor = connection.cursor()
                                                    for raw_l in raw_list:
                                                        cursor.execute('INSERT  INTO designad_designadlist_deviceslist(designadlist_id,devices_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')
                                                else:
                                                    cursor = connection.cursor()
                                                    for browser in ad_result.get('devices').get('targeted'):
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_deviceslist(designadlist_id,devices_id) VALUE(' +
                                                            str(ad_design[0].id) + ',' + str(browser.get('id')) + ')')

                                                if ad_result.get('languages') == []:
                                                    raw_list = DesignAdList.objects.raw(
                                                        'SELECT id FROM designad_languages')
                                                    cursor = connection.cursor()
                                                    for raw_l in raw_list:
                                                        cursor.execute('INSERT  INTO designad_designadlist_languageslist(designadlist_id,languages_id)'
                                                                                 ' VALUE('+str(ad_design[0].id)+','+str(raw_l.id)+')')
                                                else:
                                                    cursor = connection.cursor()
                                                    for day in ad_result.get('languages').get('targeted'):
                                                        cursor.execute(
                                                            'INSERT  INTO designad_designadlist_languageslist(designadlist_id,languages_id) VALUE(' +
                                                            str(ad_design[0].id) + ',' + str(day.get('id')) + ')')

                                                if ad_result.get('day_parting') != []:
                                                    for day in ad_result.get('day_parting'):
                                                        if day['day'] == 1:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_mondaylist(designadlist_id,monday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')
                                                        if day['day'] == 2:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_tuesdaylist(designadlist_id,tuesday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')

                                                        if day['day'] == 3:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_wednesdaylist(designadlist_id,wednesday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')
                                                        if day['day'] == 4:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_thursdaylist(designadlist_id,thursday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')
                                                        if day['day'] == 5:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_fridaylist(designadlist_id,friday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')
                                                        if day['day'] == 6:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_saturdaylist(designadlist_id,saturday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')
                                                        if day['day'] == 7:
                                                            cursor = connection.cursor()
                                                            for hour in day['hours']:
                                                                cursor.execute(
                                                                    'INSERT  INTO designad_designadlist_sundaylist(designadlist_id,sunday_id) VALUE(' +
                                                                    str(ad_design[0].id) + ',' + str(int(hour)+2) + ')')

                                                if ad_result.get('variations') != []:
                                                    for variation in ad_result.get('variations'):
                                                        adVariation = AdVariation()
                                                        adVariation.adCampaigns_id = ad_design[0].id
                                                        adVariation.user_id = userId
                                                        adVariation.varId = variation.get('idvariation')
                                                        adVariation.name = variation.get('name')
                                                        adVariation.description = variation.get('description')
                                                        adVariation.status = variation.get('status')
                                                        adVariation.url = variation.get('url')
                                                        # 添加url到外联表
                                                        if AdUrl.objects.filter(adCampaigns_id=ad_design[0].id,
                                                                                url=variation.get('url')).count() <= 0:
                                                            adUrl = AdUrl()
                                                            adUrl.user_id = userId
                                                            adUrl.adCampaigns_id = ad_design[0].id
                                                            adUrl.url = variation.get('url')
                                                            AdUrl.save(adUrl)

                                                        if variation.get('idvariations_file') != None:
                                                            adVariation.id_library_file = variation.get('idvariations_file')
                                                            # 添加创意ID到外联表
                                                            if AdCreative.objects.filter(adCampaigns_id=ad_design[0].id,
                                                                                         id_library_file=variation.get('idvariations_file')).count() <= 0:
                                                                adCreative = AdCreative()
                                                                adCreative.user_id = userId
                                                                adCreative.adCampaigns_id = ad_design[0].id
                                                                adCreative.id_library_file = variation.get('idvariations_file')
                                                                AdCreative.save(adCreative)

                                                            vari = UserAdFileLibrary.objects.filter(file_id=variation.get('idvariations_file'))
                                                            if vari.count() > 0:
                                                                adVariation.vari_url = str(vari[0].adMaterial.url).replace('/media/', '')
                                                            else:
                                                                adVariation.vari_url = ''
                                                        AdVariation.save(adVariation)





                        # 每次循环结束睡眠10秒
                        print('睡眠10s')
                        time.sleep(10)
                        print('睡眠结束')
        except Exception as ex:
            print(ex)

    # 获取用户创意
    def getUserAdFile(none):
        url = Constants.USER_FILE_URL
        userList = UserProfile.objects.all()
        for user in userList:
            userId = user.id
            if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                n = HttpUtils.getHttp(url, userId)
                if n is not None:
                    if n.status_code == 200:
                        body = json.loads(n.text)
                        result = body['result']
                        for userAdFile in result:

                            # if UserAdFileLibrary.objects.filter(file_id=userAdFile.get('id')).count() < 0:
                                print(userAdFile)
                                userAdFileLibrary = UserAdFileLibrary()
                                userAdFileLibrary.user_id = userId
                                userAdFileLibrary.file_id = userAdFile.get('id')
                                userAdFileLibrary.file_name = userAdFile.get('file_name')
                                userAdFileLibrary.type = userAdFile.get('type')
                                userAdFileLibrary.width = userAdFile.get('width')
                                userAdFileLibrary.height = userAdFile.get('height')
                                userAdFileLibrary.file_extension = userAdFile.get('file_extension')
                                userAdFileLibrary.file_size = userAdFile.get('file_size_public')
                                userAdFileLibrary.url = userAdFile.get('url')
                                userAdFileLibrary.created_at = userAdFile.get('created_at')
                                userAdFileLibrary.updated_at = userAdFile.get('updated_at')
                                UserAdFileLibrary.save(userAdFileLibrary)


    # 获取分类
    def getCategories(none):
        url = Constants.CATEGORIES_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for category in result:
                        # print(category)
                        categories = Categories()
                        categories.id = category.get('id')
                        categories.name = category.get('name')
                        categories.parent = category.get('parent')
                        Categories.save(categories)

    # 获取浏览器
    def getBrowsers(none):
        url = Constants.BROWSERS_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    print(result)
                    for browser in result:
                        # print(browser)
                        browsers = Browsers()
                        browsers.id = browser.get('id')
                        browsers.name = browser.get('name')
                        Browsers.save(browsers)

    # 获取设备
    def getDevices(none):
        url = Constants.DEVICES_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for device in result:
                        # print(device)
                        devices = Devices()
                        devices.id = device.get('id')
                        devices.name = device.get('name')
                        Devices.save(devices)

    # 获取操作系统
    def getOperatingSystem(none):
        url = Constants.OPERATINGS_SYSTEM_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for operatingSystem in result:
                        # print(operatingSystem)
                        operatingSystems = OperatingSystems()
                        operatingSystems.id = operatingSystem.get('id')
                        operatingSystems.name = operatingSystem.get('name')
                        operatingSystems.type = operatingSystem.get('type')
                        OperatingSystems.save(operatingSystems)

    # 获取移动代理商
    def getMobileCarriers(none):
        url = Constants.CARRIERS_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for mobileCarrier in result:
                        # print(mobileCarrier)
                        mobileCarriers = MobileCarriers()
                        mobileCarriers.id = mobileCarrier.get('id')
                        mobileCarriers.name = mobileCarrier.get('name')
                        mobileCarriers.country_id = mobileCarrier.get('country_id')
                        mobileCarriers.enabled = mobileCarrier.get('enabled')
                        MobileCarriers.save(mobileCarriers)

    # 获取地理位置
    def getGeoLoc(none):
        url = Constants.COUNTRY_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for geographicLocationDate in result:
                        # print(geographicLocationDate)
                        if geographicLocationDate.get('id') != 0:
                            geographicLocation = GeographicLocation()
                            geographicLocation.id = geographicLocationDate.get('id')
                            geographicLocation.short_name = geographicLocationDate.get('short_name')
                            geographicLocation.long_name = geographicLocationDate.get('long_name')
                            geographicLocation.iso2 = geographicLocationDate.get('iso2')
                            geographicLocation.iso3 = geographicLocationDate.get('iso3')
                            GeographicLocation.save(geographicLocation)

    # 获取语言
    def getLanguages(none):
        url = Constants.LANGUAGES_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for lang in result:
                        # print(lang)
                        languages = Languages()
                        languages.id = lang.get('id')
                        languages.name = lang.get('name')
                        languages.iso2 = lang.get('iso2')
                        languages.iso2_two = lang.get('iso2_two')
                        languages.save(lang)

    # 获取最低价格
    def getMinimumPrices(none):
        url = Constants.MINIMUM_PRICES_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    # print(result)
                    for miniPrices in result:

                        minimumPrices = MinimumPrices()
                        minimumPrices.format = miniPrices.get('format')
                        minimumPrices.geo = miniPrices.get('country')
                        minimumPrices.category = miniPrices.get('category')
                        minimumPrices.network_selection_type = miniPrices.get('network_selection_type')
                        minimumPrices.web_cpc = miniPrices.get('web_cpc')
                        minimumPrices.mobile_cpc = miniPrices.get('mobile_cpc')
                        minimumPrices.web_cpm = miniPrices.get('web_cpm')
                        minimumPrices.mobile_cpm = miniPrices.get('mobile_cpm')
                        MinimumPrices.save(minimumPrices)

    # 获取广告类型
    def getAdType(none):
        url = Constants.ADVERTISER_AD_TYPES_URL
        user = UserProfile.objects.all()[0]
        if ExoProfile.objects.filter(mon_user=user.id).count() > 0:
            n = HttpUtils.getHttp(url, user.id)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['result']
                    for adType in result:
                        # print(adType)
                        advertiserAdType = AdvertiserAdType()
                        advertiserAdType.id = adType.get('id')
                        advertiserAdType.name = adType.get('name')
                        advertiserAdType.media_storage_templates = json.dumps(adType.get('media_storage_templates'))
                        AdvertiserAdType.save(advertiserAdType)

    # 获取材料状态
    def getVariStatus(campaignid, userId):
        try:
            url = Constants.CAMPAIGNS_VARIATION_GET_URL.format(campaignid)

            n = HttpUtils.getHttp(url, userId)
            print(n)
            if n is not None:
                if n.status_code == 200:
                    body = json.loads(n.text)
                    result = body['variations']
                    for adVariStatus in result:
                        ad_var = AdVariation.objects.filter(varId=adVariStatus['idvariation'])
                        if ad_var.exists() and ad_var[0].status != 2:
                            ad_var.update(status=adVariStatus['variation_status'])
                        # elif ad_var.count() <= 0 and ad_var[0].status != 2:
                        #     adVariation = AdVariation()
                        #     adVariation.name = adVariStatus['name']
                        #     adVariation.user_id = userId
                        #     adVariation.description = adVariStatus['description']
                        #     adVariation.url = adVariStatus['url']
                        #     adVariation.adCampaigns_id = DesignAdList.objects.get(campaign_id=campaignid).id
                        #     adVariation.id_library_file = adVariStatus['idvariations_file']
                        #     adVariation.status = adVariStatus['variation_status']
                        #     adVariation.varId = adVariStatus['idvariation']
                        #     AdVariation.save(adVariation)

                # 每次循环结束睡眠5秒
                time.sleep(5)
        except Exception as ex:
            print(ex)

    # 获取Goals
    def getGoals(none):
        try:
            url = Constants.GOALS_URL
            userList = UserProfile.objects.all()
            for user in userList:
                userId = user.id
                n = HttpUtils.getHttp(url, userId)
                if n is not None:
                    if n.status_code == 200:
                        if n.text.find('Goals could not be found') < 0:
                            body = json.loads(n.text)
                            result = body['result']
                            for goal in result:
                                if AdGoals.objects.filter(goalId=goal.get('id')).count() > 0:
                                    scp = '<!-- START MonAdvert  Goal Tag | '+goal.get('name')+' --><script type="text/javascript" src="http://service.monadvert.com/tag_gen.js" data-goal="' + goal.get('id') + '"></script><!-- END MonAdvert  Goal Tag | '+goal.get('name')+' -->'
                                    AdGoals.objects.filter(goalId=goal.get('id')).update(
                                        goalId=goal.get('id'),
                                        name=goal.get('name'),
                                        seq=goal.get('seq'),
                                        idUser=goal.get('iduser'),
                                        order=goal.get('order'),
                                        coa=goal.get('coa'),
                                        goalScript=scp
                                    )
                                else:
                                    adGoals = AdGoals()
                                    adGoals.goalId = goal.get('id')
                                    adGoals.name = goal.get('name')
                                    adGoals.seq = goal.get('seq')
                                    adGoals.idUser = goal.get('iduser')
                                    adGoals.user_id = userId
                                    adGoals.order = goal.get('order')
                                    adGoals.coa = goal.get('coa')
                                    scp = '<!-- START MonAdvert  Goal Tag | '+goal.get('name')+' --><script type="text/javascript" src="http://service.monadvert.com/tag_gen.js" data-goal="' + goal.get('id') + '"></script><!-- END MonAdvert  Goal Tag | '+goal.get('name')+' -->'
                                    adGoals.goalScript = scp
                                    AdGoals.save(adGoals)
                    # 每次循环结束睡眠5秒
                    time.sleep(5)
        except Exception as ex:
            print(ex)

    # 获取广告内容消费数据
    def getAdVariation(userId):
        try:
            if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                for i in range(0, 2):
                    time.sleep(10)
                    day = datetime.date.today()
                    oneday = datetime.timedelta(days=i)
                    today = day - oneday
                    # today = datetime.date.today()

                    dateurl = Constants.STATISTICS_ADVERTISER_VARIATION.format(today, today)
                    print(dateurl)
                    n = HttpUtils.getHttp(dateurl, userId)
                    if n.status_code == 200:
                        body = json.loads(n.text)
                        result = body['result']
                        if result is not []:
                            print(result)
                            adGoals_count = AdGoals.objects.filter(user_id=userId).count()
                            for variationData in result:
                                goalList = []
                                ecpaList = []
                                print(variationData.get('idvariation'))
                                ad_vari = AdVariation.objects.filter(varId=variationData.get('idvariation'))
                                if ad_vari.count() > 0:
                                    # 将其转换为时间数组
                                    dailyTimeArray = datetime.datetime.strptime(str(today),
                                                                                '%Y-%m-%d')
                                    print(dailyTimeArray)
                                    rate = ConversionUtils.getRate(userId, dailyTimeArray)
                                    deliveryRate = rate.get('deliveryRate')

                                    # 如果广告内容数据已经存在则更新数据
                                    ad_variation_d = ad_variation_data.objects.filter(id_variation=variationData.get('idvariation'),
                                                                           date=today)
                                    print(ad_variation_d.count())
                                    if ad_variation_d.count() > 0:
                                        if adGoals_count > 0:
                                            goals = variationData.get('goals').get('data')

                                            for goal in goals:
                                                goalList.append(goals[goal]['volume'])
                                                ecpaList.append(goals[goal]['ecpa'])
                                            if len(goalList) == 0:
                                                for i in range(0, 4):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 1:
                                                for i in range(0, 3):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 2:
                                                for i in range(0, 2):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 3:
                                                for i in range(0, 1):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                        else:
                                            for i in range(0, 4):
                                                goalList.append(0)
                                                ecpaList.append(0)

                                        ad_variation_d.update(
                                            cost=variationData.get('cost'),
                                            impressions=variationData.get('impressions'),
                                            clicks=variationData.get('clicks'),
                                            cpc=variationData.get('cpc'),
                                            cpm=variationData.get('cpm'),
                                            ctr=variationData.get('ctr'),
                                            G1=goalList[0],
                                            ecpa1=ecpaList[0],
                                            G2=goalList[1],
                                            ecpa2=ecpaList[1],
                                            G3=goalList[2],
                                            ecpa3=ecpaList[2],
                                            G4=goalList[3],
                                            ecpa4=ecpaList[3],
                                            )
                                    # 如果广告数据不存在则添加数据
                                    else:
                                        ad_vari_data = ad_variation_data()
                                        if adGoals_count > 0:
                                            goals = variationData.get('goals').get('data')
                                            for goal in goals:
                                                if AdGoals.objects.filter(user_id=userId).count() > 0:
                                                    for i in range(0, AdGoals.objects.filter(user_id=userId).count()):
                                                        if i == 0:
                                                            ad_vari_data.G1 = goals[goal]['volume']
                                                            ad_vari_data.ecpa1 = goals[goal]['ecpa']
                                                        elif i == 1:
                                                            ad_vari_data.G2 = goals[goal]['volume']
                                                            ad_vari_data.ecpa2 = goals[goal]['ecpa']
                                                        elif i == 2:
                                                            ad_vari_data.G3 = goals[goal]['volume']
                                                            ad_vari_data.ecpa3 = goals[goal]['ecpa']
                                                        elif i == 3:
                                                            ad_vari_data.G4 = goals[goal]['volume']
                                                            ad_vari_data.ecpa4 = goals[goal]['ecpa']

                                        ad_vari_data.user_id = ad_vari[0].user_id
                                        ad_vari_data.id_variation = variationData.get('idvariation')
                                        ad_vari_data.cost = variationData.get('cost')
                                        ad_vari_data.impressions = variationData.get('impressions')
                                        ad_vari_data.clicks = variationData.get('clicks')
                                        ad_vari_data.cpc = variationData.get('cpc')
                                        ad_vari_data.cpm = variationData.get('cpm')
                                        ad_vari_data.ctr = variationData.get('ctr')
                                        ad_vari_data.deliveryRate = deliveryRate
                                        ad_vari_data.date = today

                                        ad_variation_data.save(ad_vari_data)
                    elif n.status_code == 429:
                        return 0
                return 1

        except Exception as ex:
            print(ex)

    # 获取广告日消费数据
    def getAdStatistics(campaignid, userId):
        try:
            if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                for i in range(0, 2):
                    time.sleep(10)
                    day = datetime.date.today()
                    oneday = datetime.timedelta(days=i)
                    today = day - oneday
                    # today = datetime.date.today()

                    dateurl = Constants.STATISTICS_ADVERTISER_DATE.format(campaignid, today, today)
                    print(dateurl)
                    n = HttpUtils.getHttp(dateurl, userId)
                    if n.status_code == 200:
                        body = json.loads(n.text)
                        result = body['result']
                        if result is not []:
                            print(result)
                            adGoals_count = AdGoals.objects.filter(user_id=userId).count()
                            for costDailyData in result:
                                goalList = []
                                ecpaList = []
                                if DesignAdList.objects.filter(campaign_id=int(campaignid)).count() > 0:
                                    # 将其转换为时间数组
                                    dailyTimeArray = datetime.datetime.strptime(costDailyData.get('ddate'), '%Y-%m-%d')
                                    print(dailyTimeArray)
                                    campaignId = DesignAdList.objects.filter(campaign_id=campaignid)[0].id
                                    rate = ConversionUtils.getRate(userId, dailyTimeArray)
                                    deliveryRate = rate.get('deliveryRate')
                                    # 如果广告数据已经存在则更新数据
                                    cost_daily_trend = CostDailyTrendReport.objects.filter(adCampaigns_id=campaignId,
                                                                           date=costDailyData.get('ddate'))
                                    if cost_daily_trend.count() > 0:

                                        cost_daily_trend.update(
                                            cost=costDailyData.get('cost'),
                                            deliveryRate=deliveryRate,
                                            )
                                    # 如果广告数据不存在则添加数据
                                    else:
                                        if DesignAdList.objects.filter(campaign_id=campaignid).count() > 0:
                                            costDailyTrendReport = CostDailyTrendReport()
                                            costDailyTrendReport.adUser_id = DesignAdList.objects.filter(campaign_id=campaignid)[0].adUser_id
                                            costDailyTrendReport.adCampaigns_id = campaignId
                                            costDailyTrendReport.cost = costDailyData.get('cost')
                                            costDailyTrendReport.deliveryRate = deliveryRate
                                            costDailyTrendReport.date = costDailyData.get('ddate')

                                            CostDailyTrendReport.save(costDailyTrendReport)

                                    if costDailyData.get('clicks') != 0:
                                        avgCpc = float(costDailyData.get('cost') / costDailyData.get('clicks'))
                                    else:
                                        avgCpc = '0.00'

                                    if costDailyData.get('ctr') != 0:
                                        dailyCtr = round(costDailyData.get('ctr'), 2)
                                        print('换算日CTR：：：：：'+str(dailyCtr)+'  原始日CTR::::'+ str(costDailyData.get('ctr')))
                                    else:
                                        dailyCtr = 0.00
                                    cost_daily = CostDailyReport.objects.filter(adCampaigns_id=campaignId, date=costDailyData.get('ddate'))
                                    if cost_daily.count() > 0:
                                        if adGoals_count > 0:
                                            goals = costDailyData.get('goals').get('data')

                                            for goal in goals:
                                                goalList.append(goals[goal]['volume'])
                                                ecpaList.append(goals[goal]['ecpa'])
                                            if len(goalList) == 0:
                                                for i in range(0, 4):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 1:
                                                for i in range(0, 3):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 2:
                                                for i in range(0, 2):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                            elif len(goalList) == 3:
                                                for i in range(0, 1):
                                                    goalList.append(0)
                                                    ecpaList.append(0)
                                        else:
                                            for i in range(0, 4):
                                                goalList.append(0)
                                                ecpaList.append(0)

                                        cost_daily.update(
                                            cost=costDailyData.get('cost'),
                                            impressions=costDailyData.get('impressions'),
                                            clicks=costDailyData.get('clicks'),
                                            avgCpc=avgCpc,
                                            deliveryRate=deliveryRate,
                                            ctr=dailyCtr,
                                            G1=goalList[0],
                                            ecpa1=ecpaList[0],
                                            G2=goalList[1],
                                            ecpa2=ecpaList[1],
                                            G3=goalList[2],
                                            ecpa3=ecpaList[2],
                                            G4=goalList[3],
                                            ecpa4=ecpaList[3],
                                            )
                                    else:
                                        costDailyReport = CostDailyReport()
                                        if adGoals_count > 0:
                                            goals = costDailyData.get('goals').get('data')
                                            for goal in goals:
                                                if AdGoals.objects.filter(user_id=userId).count() > 0:

                                                    for i in range(0, AdGoals.objects.filter(user_id=userId).count()):
                                                        if i == 0:
                                                            costDailyReport.G1 = goals[goal]['volume']
                                                            costDailyReport.ecpa1 = goals[goal]['ecpa']

                                                        elif i == 1:
                                                            costDailyReport.G2 = goals[goal]['volume']
                                                            costDailyReport.ecpa2 = goals[goal]['ecpa']

                                                        elif i == 2:
                                                            costDailyReport.G3 = goals[goal]['volume']
                                                            costDailyReport.ecpa3 = goals[goal]['ecpa']

                                                        elif i == 3:
                                                            costDailyReport.G4 = goals[goal]['volume']
                                                            costDailyReport.ecpa4 = goals[goal]['ecpa']

                                        costDailyReport.adUser_id = DesignAdList.objects.filter(campaign_id=campaignid)[0].adUser_id
                                        costDailyReport.adCampaigns_id = campaignId
                                        costDailyReport.cost = costDailyData.get('cost')
                                        costDailyReport.impressions = costDailyData.get('impressions')
                                        costDailyReport.clicks = costDailyData.get('clicks')
                                        costDailyReport.avgCpc = avgCpc
                                        costDailyReport.ctr = dailyCtr
                                        costDailyReport.deliveryRate = deliveryRate
                                        costDailyReport.date = costDailyData.get('ddate')
                                        CostDailyReport.save(costDailyReport)
                    elif n.status_code == 429:
                        return 0
                return 1

        except Exception as ex:
            print(ex)

    # 获取分时数据
    def getHourData(campaignid, userId):
        try:
            if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                for i in range(0, 2):
                    day = datetime.date.today()
                    oneday = datetime.timedelta(days=i)
                    today = day - oneday #datetime.date.today()
                    # yesterday = today - datetime.timedelta(days=1)
                    hoururl = Constants.STATISTICS_ADVERTISER_HOUR.format(campaignid, today, today)
                    print(hoururl)
                    n = HttpUtils.getHttp(hoururl, userId)
                    print(n)
                    if n.status_code == 200:
                        body = json.loads(n.text)
                        result = body.get('result')
                        print('时间：：{0}'.format(result))
                        if result is not []:
                            adGoals_count = AdGoals.objects.filter(user_id=userId).count()
                            for costHourData in result:
                                goalList = []
                                ecpaList = []
                                dateTime = datetime.datetime.strptime(str(today) + ' ' + str(costHourData.get('hour')),
                                                                      '%Y-%m-%d %H')
                                rate = ConversionUtils.getRate(userId, dateTime)
                                deliveryRate = rate.get('deliveryRate')
                                print(dateTime)
                                campaignId = DesignAdList.objects.filter(campaign_id=campaignid)[0].id
                                if CostHourTrendReport.objects.filter(
                                        adCampaigns_id=campaignId,
                                        date=dateTime).count() > 0:
                                    CostHourTrendReport.objects.filter(adCampaigns_id=campaignId, date=dateTime).update(
                                        cost=costHourData.get('cost'),
                                        deliveryRate=deliveryRate
                                    )
                                else:
                                    # 存储已获取过的广告数据，获取时间的数据需要判断，每日的数据不用
                                    # write_adrecode_to_cache(str(campaignid).join(str(timeStamp)), 1)
                                    print(campaignid)
                                    if DesignAdList.objects.filter(campaign_id=campaignid).count() > 0:
                                        costHourTrendReport = CostHourTrendReport()

                                        costHourTrendReport.adUser_id = DesignAdList.objects.filter(campaign_id=campaignid)[0].adUser_id
                                        costHourTrendReport.adCampaigns_id = campaignId
                                        costHourTrendReport.cost = costHourData.get('cost')
                                        costHourTrendReport.deliveryRate = deliveryRate
                                        costHourTrendReport.date = dateTime

                                        CostHourTrendReport.save(costHourTrendReport)

                                if costHourData.get('clicks') != 0:
                                    avgCpc = (costHourData.get('cost') / costHourData.get('clicks'))
                                else:
                                    avgCpc = '0.00'

                                if costHourData.get('ctr') != 0:
                                    hourCtr = round(costHourData.get('ctr'), 2)
                                    print('换算分时CTR：：：：：' + str(hourCtr) + '  原始分时CTR::::' + str(costHourData.get('ctr')))
                                else:
                                    hourCtr = 0.00
                                if CostHourReport.objects.filter(
                                        adCampaigns_id=DesignAdList.objects.filter(campaign_id=campaignid)[0].id,
                                        date=dateTime).count() > 0:
                                    if adGoals_count > 0:
                                        goals = costHourData.get('goals').get('data')

                                        for goal in goals:
                                            goalList.append(goals[goal]['volume'])
                                            ecpaList.append(goals[goal]['ecpa'])
                                        if len(goalList) == 0:
                                            for i in range(0, 4):
                                                goalList.append(0)
                                                ecpaList.append(0)
                                        elif len(goalList) == 1:
                                            for i in range(0, 3):
                                                goalList.append(0)
                                                ecpaList.append(0)
                                        elif len(goalList) == 2:
                                            for i in range(0, 2):
                                                goalList.append(0)
                                                ecpaList.append(0)
                                        elif len(goalList) == 3:
                                            for i in range(0, 1):
                                                goalList.append(0)
                                                ecpaList.append(0)
                                    else:
                                        for i in range(0, 4):
                                            goalList.append(0)
                                            ecpaList.append(0)
                                    CostHourReport.objects.filter(
                                        adCampaigns_id=DesignAdList.objects.filter(campaign_id=campaignid)[0].id,
                                        date=dateTime).update(
                                        cost=costHourData.get('cost'),
                                        impressions=costHourData.get('impressions'),
                                        clicks=costHourData.get('clicks'),
                                        avgCpc=avgCpc,
                                        deliveryRate=deliveryRate,
                                        ctr=hourCtr,
                                        G1=goalList[0],
                                        ecpa1=ecpaList[0],
                                        G2=goalList[1],
                                        ecpa2=ecpaList[1],
                                        G3=goalList[2],
                                        ecpa3=ecpaList[2],
                                        G4=goalList[3],
                                        ecpa4=ecpaList[3],
                                    )
                                else:
                                    print(campaignid)
                                    if DesignAdList.objects.filter(campaign_id=campaignid).count() > 0:
                                        costHourReport = CostHourReport()
                                        if adGoals_count > 0:
                                            goals = costHourData.get('goals').get('data')
                                            for goal in goals:
                                                if AdGoals.objects.filter(user_id=userId).count() > 0:

                                                    for i in range(0, AdGoals.objects.filter(user_id=userId).count()):
                                                        if i == 0:
                                                            costHourReport.G1 = goals[goal]['volume']
                                                            costHourReport.ecpa1 = goals[goal]['ecpa']

                                                        elif i == 1:
                                                            costHourReport.G2 = goals[goal]['volume']
                                                            costHourReport.ecpa2 = goals[goal]['ecpa']

                                                        elif i == 2:
                                                            costHourReport.G3 = goals[goal]['volume']
                                                            costHourReport.ecpa3 = goals[goal]['ecpa']

                                                        elif i == 3:
                                                            costHourReport.G4 = goals[goal]['volume']
                                                            costHourReport.ecpa4 = goals[goal]['ecpa']
                                        costHourReport.adUser_id = DesignAdList.objects.filter(campaign_id=campaignid)[0].adUser_id
                                        costHourReport.adCampaigns_id = DesignAdList.objects.filter(campaign_id=campaignid)[0].id
                                        costHourReport.cost = costHourData.get('cost')
                                        costHourReport.date = dateTime
                                        costHourReport.impressions = costHourData.get('impressions')
                                        costHourReport.clicks = costHourData.get('clicks')
                                        costHourReport.avgCpc = avgCpc
                                        costHourReport.deliveryRate = deliveryRate
                                        costHourReport.ctr = hourCtr

                                        CostHourReport.save(costHourReport)

                    elif n.status_code == 429:
                        return 0
                return 1
        except Exception as ex:
            print(ex)

    # 获取站点数据
    def getSitesData(userId):
        try:
            if ExoProfile.objects.filter(mon_user=userId).count() > 0:
                for i in range(0, 2):
                    day = datetime.date.today()
                    oneday = datetime.timedelta(days=i)
                    today = day-oneday #datetime.date.today()
                    countSites = HttpUtils.getHttp(Constants.STATISTICS_ADVERTISER_SITES_COUNT.format(today, today), userId)
                    if countSites.status_code == 200 and not type(json.loads(countSites.text).get('request_metadata')) is list:
                        count = json.loads(countSites.text).get('request_metadata').get('count')
                        # print(count)
                        num = int(count/1000) + 1
                        print(num)
                        for j in range(0, num):
                            sitesUrl = Constants.STATISTICS_ADVERTISER_SITES_GOALS.format(today, today, (j * 1000))
                            print(sitesUrl)
                            n = HttpUtils.getHttp(sitesUrl, userId)
                            # print(n)
                            if n is not None:
                                print(n.status_code)
                                if n.status_code == 200:
                                    # 转时间
                                    dailyTimeArray = datetime.datetime.strptime(str(today),
                                                                                '%Y-%m-%d')
                                    print(dailyTimeArray)
                                    rate = ConversionUtils.getRate(userId, dailyTimeArray)
                                    deliveryRate = rate.get('deliveryRate')

                                    body = json.loads(n.text)
                                    result = body.get('result')
                                    # print(result)
                                    if result is not []:
                                        adGoals_count = AdGoals.objects.filter(user_id=userId).count()
                                        # print(adGoals_count)
                                        for siteData in result:
                                            goalList = []
                                            ecpaList = []
                                            # print(siteData)

                                            if float(siteData.get('ctr')) != 0:
                                                siteCtr = round(float(siteData.get('ctr')), 2)
                                                # print('站点CTR：：：：：' + str(siteCtr) + '  站点CTR::::' + str(siteData.get('ctr')))
                                            else:
                                                siteCtr = 0.00

                                            if str(siteData.get('site_hostname')).find('exoclick') > 0:
                                                site_hostname = 'http://service.monadvert.com/'

                                            else:
                                                site_hostname = siteData.get('site_hostname')
                                            if DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign')).count() > 0:
                                                if SitesData.objects.filter(adCampaigns_id=DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign'))[0].id,
                                                                            site_hostname=site_hostname,
                                                                            date=today).count() > 0:

                                                    if adGoals_count > 0:
                                                        goals = siteData.get('goals').get('data')

                                                        for goal in goals:
                                                            goalList.append(goals[goal]['volume'])
                                                            ecpaList.append(goals[goal]['ecpa'])
                                                        if len(goalList) == 0:
                                                            for i in range(0, 4):
                                                                goalList.append(0)
                                                                ecpaList.append(0)
                                                        elif len(goalList) == 1:
                                                            for i in range(0, 3):
                                                                goalList.append(0)
                                                                ecpaList.append(0)
                                                        elif len(goalList) == 2:
                                                            for i in range(0, 2):
                                                                goalList.append(0)
                                                                ecpaList.append(0)
                                                        elif len(goalList) == 3:
                                                            for i in range(0, 1):
                                                                goalList.append(0)
                                                                ecpaList.append(0)
                                                    else:
                                                        for i in range(0, 4):
                                                            goalList.append(0)
                                                            ecpaList.append(0)
                                                    if siteData.get('cpc') != 0:
                                                        avgCpc = (siteData.get('cost') / siteData.get('clicks'))

                                                    else:
                                                        avgCpc = '0.00'

                                                    if siteData.get('impressions') != 0:
                                                        avgCpm = siteData.get('cost') / (siteData.get('impressions')/1000)
                                                    else:
                                                        avgCpm = '0.00'
                                                    # print('更新：：：' + site_hostname)
                                                    SitesData.objects.filter(adCampaigns_id=DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign'))[0].id,
                                                                                       site_hostname=site_hostname,
                                                                                       date=today).update(cost=siteData.get('cost'),
                                                                                                          clicks=siteData.get('clicks'),
                                                                                                          impressions=siteData.get('impressions'),
                                                                                                          adCampaigns_id=DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign'))[0].id,
                                                                                                          ctr=siteCtr,
                                                                                                          cpm=siteData.get('cpm'),
                                                                                                          cpv=siteData.get('cpv'),
                                                                                                          cpc=siteData.get('cpc'),
                                                                                                          avgCpc=avgCpc,
                                                                                                          avgCpm=avgCpm,
                                                                                                          deliveryRate=deliveryRate,
                                                                                                          G1=goalList[0],
                                                                                                          ecpa1=ecpaList[0],
                                                                                                          G2=goalList[1],
                                                                                                          ecpa2=ecpaList[1],
                                                                                                          G3=goalList[2],
                                                                                                          ecpa3=ecpaList[2],
                                                                                                          G4=goalList[3],
                                                                                                          ecpa4=ecpaList[3],
                                                                                                          date=str(today)
                                                                                                          )
                                                elif SitesData.objects.filter(adCampaigns_id=DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign'))[0].id,
                                                                            site_hostname=site_hostname,
                                                                            date=today).count() <= 0:
                                                    # 存储已获取过的广告数据，获取时间的数据需要判断，每日的数据不用
                                                    # write_adrecode_to_cache(str(campaignid).join(str(timeStamp)), 1)
                                                    # print('插入：：：' + site_hostname)

                                                    sitesData = SitesData()
                                                    if adGoals_count > 0:
                                                        goals = siteData.get('goals').get('data')
                                                        for goal in goals:
                                                            if AdGoals.objects.filter(user_id=userId).count() > 0:

                                                                for i in range(0, AdGoals.objects.filter(user_id=userId).count()):
                                                                    if i == 0:
                                                                        sitesData.G1 = goals[goal]['volume']
                                                                        sitesData.ecpa1 = goals[goal]['ecpa']
                                                                        # print(goal)
                                                                        # print(G1)
                                                                        # print(ecpa1)
                                                                    elif i == 1:
                                                                        sitesData.G2 = goals[goal]['volume']
                                                                        sitesData.ecpa2 = goals[goal]['ecpa']
                                                                        # print('g2::' + str(G2))
                                                                        # print(ecpa2)
                                                                    elif i == 2:
                                                                        sitesData.G3 = goals[goal]['volume']
                                                                        sitesData.ecpa3 = goals[goal]['ecpa']

                                                                        # print('g3::' + str(G3))
                                                                        # print(ecpa3)
                                                                    elif i == 3:
                                                                        sitesData.G4 = goals[goal]['volume']
                                                                        sitesData.ecpa4 = goals[goal]['ecpa']

                                                                        # print('g4::' + str(G4))
                                                                        # print(ecpa4)
                                                    # print(siteData)

                                                    sitesData.site_hostname = site_hostname
                                                    sitesData.cost = siteData.get('cost')
                                                    sitesData.deliveryRate = deliveryRate
                                                    sitesData.clicks = siteData.get('clicks')
                                                    sitesData.impressions = siteData.get('impressions')
                                                    sitesData.adCampaigns_id = DesignAdList.objects.filter(campaign_id=siteData.get('idcampaign'))[0].id

                                                    sitesData.ctr = siteCtr
                                                    sitesData.cpm = siteData.get('cpm')
                                                    sitesData.cpv = siteData.get('cpv')
                                                    sitesData.cpc = siteData .get('cpc')
                                                    if siteData.get('cpc') != 0:
                                                        sitesData.avgCpc = (siteData.get('cost') / siteData.get('clicks'))
                                                    else:
                                                        sitesData.avgCpc = '0.00'

                                                    if siteData.get('impressions') != 0:
                                                        sitesData.avgCpm = siteData.get('cost') / (siteData.get('impressions') / 1000)

                                                    else:
                                                        sitesData.avgCpm = '0.00'

                                                    sitesData.impressions = siteData.get('impressions')
                                                    sitesData.user_id = userId
                                                    sitesData.date = str(today)
                                                    SitesData.save(sitesData)
                    elif countSites.status_code == 429:
                        return 0
                return 1
        except Exception as ex:
            print(ex)

    # 更新站点数据临时表数据
    def updateSitesTempData(userId):
        try:
            # 今天日期
            day = datetime.date.today()
            # 今年一月一号
            day1 = datetime.date(datetime.date.today().year, 1, 1)
            # 本月一号
            cur_date_first = datetime.date(datetime.date.today().year, datetime.date.today().month, 1)
            # 过去7天
            week_day = day - datetime.timedelta(days=7)

            # 更新过去七天
            raw_list = InitData.sql_get_sites_date(userId, week_day, day)
            # print(raw_list.query)
            for d in raw_list:
                is_data = SitesTempData.objects.filter(user_id=userId, adCampaigns_id=d.adCampaigns_id, site_hostname=d.site_hostname, type=1)
                if is_data.count() > 0:
                    site_date = is_data.get(id=is_data[0].id)
                    site_date.cost = d.cost
                    site_date.clicks = d.clicks
                    site_date.impressions = d.impressions
                    site_date.ctr = d.ctr
                    site_date.avgCpc = d.avgCpc
                    site_date.avgCpm = d.avgCpm
                    site_date.G1 = d.G1
                    site_date.ecpa1 = d.ecpa1
                    site_date.G2 = d.G2
                    site_date.ecpa2 = d.ecpa2
                    site_date.G3 = d.G3
                    site_date.ecpa3 = d.ecpa3
                    site_date.G4 = d.G4
                    site_date.ecpa4 = d.ecpa4
                    site_date.date=str(week_day) + '/' + str(day)
                    site_date.save()

                elif is_data.count() <= 0:
                    sitesTempData = SitesTempData()
                    sitesTempData.user_id = userId
                    sitesTempData.adCampaigns_id = d.adCampaigns_id
                    sitesTempData.site_hostname = d.site_hostname
                    sitesTempData.cost = d.cost
                    sitesTempData.clicks = d.clicks
                    sitesTempData.impressions = d.impressions
                    sitesTempData.ctr = d.ctr
                    sitesTempData.avgCpc = d.avgCpc
                    sitesTempData.avgCpm = d.avgCpm
                    sitesTempData.G1 = d.G1
                    sitesTempData.ecpa1 = d.ecpa1
                    sitesTempData.G2 = d.G2
                    sitesTempData.ecpa2 = d.ecpa2
                    sitesTempData.G3 = d.G3
                    sitesTempData.ecpa3 = d.ecpa3
                    sitesTempData.G4 = d.G4
                    sitesTempData.ecpa4 = d.ecpa4
                    sitesTempData.type = 1
                    sitesTempData.date = str(week_day) + '/' + str(day)
                    sitesTempData.save()


            # 更新本月
            raw_list = InitData.sql_get_sites_date(userId, cur_date_first, day)
            # print(raw_list.query)
            for d in raw_list:
                is_data = SitesTempData.objects.filter(user_id=userId, adCampaigns_id=d.adCampaigns_id,
                                                       site_hostname=d.site_hostname, type=2)

                if is_data.count() > 0:
                    site_date = is_data.get(id=is_data[0].id)
                    site_date.cost = d.cost
                    site_date.clicks = d.clicks
                    site_date.impressions = d.impressions
                    site_date.ctr = d.ctr
                    site_date.avgCpc = d.avgCpc
                    site_date.avgCpm = d.avgCpm
                    site_date.G1 = d.G1
                    site_date.ecpa1 = d.ecpa1
                    site_date.G2 = d.G2
                    site_date.ecpa2 = d.ecpa2
                    site_date.G3 = d.G3
                    site_date.ecpa3 = d.ecpa3
                    site_date.G4 = d.G4
                    site_date.ecpa4 = d.ecpa4
                    site_date.date=str(cur_date_first) + '/' + str(day)
                    site_date.save()
                elif is_data.count() <= 0:
                    sitesTempData_1 = SitesTempData()

                    sitesTempData_1.user_id = userId
                    sitesTempData_1.adCampaigns_id = d.adCampaigns_id
                    sitesTempData_1.site_hostname = d.site_hostname
                    sitesTempData_1.cost = d.cost
                    sitesTempData_1.clicks = d.clicks
                    sitesTempData_1.impressions = d.impressions
                    sitesTempData_1.ctr = d.ctr
                    sitesTempData_1.avgCpc = d.avgCpc
                    sitesTempData_1.avgCpm = d.avgCpm
                    sitesTempData_1.G1 = d.G1
                    sitesTempData_1.ecpa1 = d.ecpa1
                    sitesTempData_1.G2 = d.G2
                    sitesTempData_1.ecpa2 = d.ecpa2
                    sitesTempData_1.G3 = d.G3
                    sitesTempData_1.ecpa3 = d.ecpa3
                    sitesTempData_1.G4 = d.G4
                    sitesTempData_1.ecpa4 = d.ecpa4
                    sitesTempData_1.type = 2
                    sitesTempData_1.date = str(cur_date_first) + '/' + str(day)
                    sitesTempData_1.save()

            # 更新上一个月
            raw_list = InitData.sql_get_sites_date(userId, InitData.get_first_day_of_last_month(0), InitData.get_last_day_of_last_month(0))
            # print(raw_list.query)
            for d in raw_list:
                is_data = SitesTempData.objects.filter(user_id=userId, adCampaigns_id=d.adCampaigns_id,
                                                       site_hostname=d.site_hostname, type=4)

                if is_data.count() > 0:
                    site_date = is_data.get(id=is_data[0].id)
                    site_date.cost=d.cost
                    site_date.clicks = d.clicks
                    site_date.impressions = d.impressions
                    site_date.ctr = d.ctr
                    site_date.avgCpc = d.avgCpc
                    site_date.avgCpm = d.avgCpm
                    site_date.G1 = d.G1
                    site_date.ecpa1 = d.ecpa1
                    site_date.G2 = d.G2
                    site_date.ecpa2 = d.ecpa2
                    site_date.G3 = d.G3
                    site_date.ecpa3 = d.ecpa3
                    site_date.G4 = d.G4
                    site_date.ecpa4 = d.ecpa4
                    site_date.date=str(InitData.get_first_day_of_last_month(0)) + '/' + str(InitData.get_last_day_of_last_month(0))
                    site_date.save()
                elif is_data.count() <= 0:
                    sitesTempData_3 = SitesTempData()

                    sitesTempData_3.user_id = userId
                    sitesTempData_3.adCampaigns_id = d.adCampaigns_id
                    sitesTempData_3.site_hostname = d.site_hostname
                    sitesTempData_3.cost = d.cost
                    sitesTempData_3.clicks = d.clicks
                    sitesTempData_3.impressions = d.impressions
                    sitesTempData_3.ctr = d.ctr
                    sitesTempData_3.avgCpc = d.avgCpc
                    sitesTempData_3.avgCpm = d.avgCpm
                    sitesTempData_3.G1 = d.G1
                    sitesTempData_3.ecpa1 = d.ecpa1
                    sitesTempData_3.G2 = d.G2
                    sitesTempData_3.ecpa2 = d.ecpa2
                    sitesTempData_3.G3 = d.G3
                    sitesTempData_3.ecpa3 = d.ecpa3
                    sitesTempData_3.G4 = d.G4
                    sitesTempData_3.ecpa4 = d.ecpa4
                    sitesTempData_3.type = 4
                    sitesTempData_3.date = str(InitData.get_first_day_of_last_month(0)) + '/' + str(
                        InitData.get_last_day_of_last_month(0))
                    sitesTempData_3.save()

            # 更新今年
            raw_list = InitData.sql_get_sites_date(userId, day1, day)
            # print(raw_list.query)
            for d in raw_list:
                is_data = SitesTempData.objects.filter(user_id=userId, adCampaigns_id=d.adCampaigns_id,
                                                       site_hostname=d.site_hostname, type=3)
                if is_data.count() > 0:
                    site_date = is_data.get(id=is_data[0].id)
                    site_date.cost=d.cost
                    site_date.clicks=d.clicks
                    site_date.impressions=d.impressions
                    site_date.ctr=d.ctr
                    site_date.avgCpc=d.avgCpc
                    site_date.avgCpm=d.avgCpm
                    site_date.G1=d.G1
                    site_date.ecpa1=d.ecpa1
                    site_date.G2=d.G2
                    site_date.ecpa2=d.ecpa2
                    site_date.G3=d.G3
                    site_date.ecpa3=d.ecpa3
                    site_date.G4=d.G4
                    site_date.ecpa4=d.ecpa4
                    site_date.date=str(day1) + '/' + str(day)
                    site_date.save()
                elif is_data.count() <= 0:
                    sitesTempData_2 = SitesTempData()
                    sitesTempData_2.user_id = userId
                    sitesTempData_2.adCampaigns_id = d.adCampaigns_id
                    sitesTempData_2.site_hostname = d.site_hostname
                    sitesTempData_2.cost = d.cost
                    sitesTempData_2.clicks = d.clicks
                    sitesTempData_2.impressions = d.impressions
                    sitesTempData_2.ctr = d.ctr
                    sitesTempData_2.avgCpc = d.avgCpc
                    sitesTempData_2.avgCpm = d.avgCpm
                    sitesTempData_2.G1 = d.G1
                    sitesTempData_2.ecpa1 = d.ecpa1
                    sitesTempData_2.G2 = d.G2
                    sitesTempData_2.ecpa2 = d.ecpa2
                    sitesTempData_2.G3 = d.G3
                    sitesTempData_2.ecpa3 = d.ecpa3
                    sitesTempData_2.G4 = d.G4
                    sitesTempData_2.ecpa4 = d.ecpa4
                    sitesTempData_2.type = 3
                    sitesTempData_2.date = str(day1) + '/' + str(day)
                    sitesTempData_2.save()
        except Exception as ex:
            print(ex)

    def sql_get_sites_date(userId, gte_time, lt_time):
        print(gte_time)
        print(lt_time)
        return SitesData.objects \
            .raw('select s.id,s.user_id,s.adCampaigns_id,s.site_hostname,'
                 'convert(SUM(s.cost/s.deliveryRate),decimal(10,2)) as cost,'
                 'SUM(s.clicks) as clicks, SUM(s.impressions) as impressions,'
                 'convert(case when SUM(s.impressions)<=0 then 0 else SUM(s.clicks)*100/SUM(s.impressions) end,decimal(10,2)) as ctr,'
                 'convert(case when SUM(s.clicks)<=0 then 0 else SUM(s.cost/s.deliveryRate)/SUM(s.clicks) end,decimal(10,3)) as avgCpc,'
                 'convert(case when SUM(s.impressions)<=0 then 0 else SUM(s.cost/s.deliveryRate)*1000/SUM(s.impressions) end,decimal(10,3)) as avgCpm,'
                 'convert(case when SUM(s.G1)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G1) end,decimal(10,2)) as ecpa1,convert(SUM(s.G1),decimal(10,2)) as G1,'
                 'convert(case when SUM(s.G2)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G2) end,decimal(10,2)) as ecpa2,convert(SUM(s.G2),decimal(10,2)) as G2,'
                 'convert(case when SUM(s.G3)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G3) end,decimal(10,2)) as ecpa3,convert(SUM(s.G3),decimal(10,2)) as G3,'
                 'convert(case when SUM(s.G4)<=0 then 0.00 else SUM(s.cost/s.deliveryRate)/SUM(s.G4) end,decimal(10,2)) as ecpa4,convert(SUM(s.G4),decimal(10,2)) as G4  from designad_sitesdata s '
                 ' where  s.user_id=' + str(userId)
                 + ' AND s.date BETWEEN "' + str(gte_time) + '" AND "' + str(lt_time)
                 + '" group by s.adCampaigns_id,s.site_hostname'
                 )

    def get_first_day_of_last_month(none):
        d = datetime.datetime.now()

        year = d.year
        month = d.month

        if month == 1:
            month = 12
            year -= 1
        else:
            month -= 1
        return datetime.datetime(year, month, 1).strftime('%Y-%m-%d')

    def get_last_day_of_last_month(none):
        d = datetime.datetime.now()
        year = d.year
        month = d.month
        if month == 1:
            month = 12
            year -= 1
        else:
            month -= 1
        days = calendar.monthrange(year, month)[1]
        return datetime.datetime(year, month, days).strftime('%Y-%m-%d')